package com.asset.modules.common.servcie;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.asset.modules.assets.model.Asset;
import com.asset.modules.common.handler.AssetAmountHandler;
import com.asset.modules.common.handler.AssetSheetWriteHandler;
import com.asset.modules.common.model.AmountBean;
import com.asset.modules.common.utils.Constant;
import com.asset.modules.common.utils.DateUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 解析Excel
 * @author chenchen
 */
public class OperateExcelService {

    private static final Logger LOGGER = LoggerFactory.getLogger(OperateExcelService.class);


    private static final String IMPORT_SUCCESS="数据导入成功！";

    private static final String IMPORT_FAILED = "数据导入失败，参数设置有误，或查询不到关联公司信息！";

    //TODO 带公式的单元格处理

    /**
     * 解析Excel，采用easyExcel
     * @param filePath  文件路径   for example: basePath + File.separator + "demo" + File.separator + "demo.xlsx";
     * @param c  Excel对象类
     * @param listener 解析处理监听类
     * @param sheetNo Excel子表下标
     * @param sheetName Excel子表名称
     * @param headRowNum 读取开始位置，目的是为了跳过表头
     */
    public static  String  ParseExcel(String filePath, Class c, AnalysisEventListener listener, int sheetNo, String sheetName, int headRowNum){
        if(filePath==null || filePath.isEmpty()) {
            LOGGER.warn("Excel读取路径出错，请排查！");
            return "error, Excel读取路径出错，请排查！";
        }else {
            String path  ="2020.04.28保利江苏2019年固定资产盘点表-办公室（完整）.xls";
            EasyExcel.read(path, c, listener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
        }
        return "success,文件读取成功，并已存入数据库!";
    }

    /**
     * 解析Excel
     * @param file
     * @param c
     * @param listener
     * @param sheetNo
     * @param headRowNum
     */
    public static String  readExcel(MultipartFile file, Class c, AnalysisEventListener listener, int sheetNo, int headRowNum){
        String result;
        try {
            EasyExcel.read(file.getInputStream(), c, listener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
            result = IMPORT_SUCCESS;
        } catch (IOException e) {
            result = IMPORT_FAILED;
            LOGGER.error(e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 读取所有Excel
     * @param file
     * @param c
     * @param listener
     * @param headRowNum
     * @return
     */
    public static String readAllExcel(MultipartFile file, Class c, AnalysisEventListener listener, int headRowNum){
        String result ;
        try {
            EasyExcel.read(file.getInputStream(), c, listener).doReadAll();
            result = IMPORT_SUCCESS;
        } catch (IOException e) {
            result = IMPORT_FAILED;
            LOGGER.error(result+": "+e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 生成Excel 文件并写出
     * @param response
     * @param c
     * @param data
     * @throws IOException
     */
    public static void writeExcel(HttpServletResponse response, Class c, String sheetName, List<Asset> data) {
        String info;
        try {
            //TODO 1、最后一栏写入合计
            //头策略使用默认
            //内容样式策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //垂直居中,水平居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 10);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            String date = DateUtils.formatDateToString(new Date(), DateUtils.DATE_FORMAT_Y)+"-12-31";
            LOGGER.info("截止日期为:"+date);
            EasyExcel.write(response.getOutputStream(), c).head(head())
                    .registerWriteHandler(new AssetSheetWriteHandler(Constant.EXCEL_TITLE, sheetName, date))
                    .registerWriteHandler(new AssetAmountHandler(assembleAmount(data)))
                    .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                    // 设置头开始写入的行
                    .useDefaultStyle(true).relativeHeadRowIndex(3)
                    .sheet(sheetName)
                    .doWrite(data);
        } catch (IOException e) {
            LOGGER.error(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 生成Excel文件并写出(带头信息)
     * @param response
     * @param c
     * @param sheetName
     * @param data
     */
    public static void writeExcelWithHead(HttpServletResponse response, Class c, String sheetName, List<Asset> data){
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(response.getOutputStream(), c).build();
            WriteSheet writeSheet  = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build();
            WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
            excelWriter.write(data, writeSheet, writeTable0);
        } catch (IOException e) {
            LOGGER.error(e.getMessage());
            e.printStackTrace();
        }finally {
            if(excelWriter!=null){
                excelWriter.finish();
            }
        }

    }
    /**
     * Excel标题
     * @return
     */
    private static List<List<String>> head(){
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("序号");
        list.add(head0);
        return list;
    }

    /**
     * 计算总计数据
     * @param data
     * @return
     */
    private static AmountBean assembleAmount(List<Asset> data){
        AmountBean ab = new AmountBean();
        int totalRegNum = 0;
        Double totalRegMoney = 0.0;
        int totalCheckedNum = 0;
        Double totalCheckedMoney = 0.0;
        int totalEarnNum = 0;
        Double totalEarnMoney =0.0;
        Double totalValue = 0.0;
        if(data.size()>0){
            for(Asset a : data){
                totalRegNum      +=  a.getRegisterNum();
                totalRegMoney    +=  a.getRegisterMoney();
                totalCheckedNum  +=  a.getCheckNum();
                totalCheckedMoney+=  a.getCheckMoney();
                totalEarnMoney   +=  a.getEarnMoney();
                totalEarnNum     +=  a.getEarnMun();
                totalValue       +=  a.getValue();
            }
        }
        ab.setTotalRegNum(totalRegNum);
        ab.setTotalRegMoney(totalRegMoney);
        ab.setTotalCheckNum(totalCheckedNum);
        ab.setTotalCheckedMoney(totalCheckedMoney);
        ab.setTotalEarnMoney(totalEarnMoney);
        ab.setTotalEarnNum(totalEarnNum);
        ab.setTotalValue(totalValue);
        ab.setTotalRecords(data.size());
        return ab;
    }
}
